﻿/*
 * 文件名：Success.cs
 * 类名：XHCTMS.DAL.Success
 * 创建人：朱震宇
 * 创建时间：2013.5.29
 * 
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace XHCTMS.DAL
{
    public class Success
    {
        /// <summary>
        /// 查询所有学生成绩
        /// </summary>
        /// <returns>返回类型DataTable 异常/null</returns>
        public DataTable GetSuccess()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select sub_name, class_name, student_name, teaching_lesson, suc_per_score,
        suc_checking_score, suc_score, teaching_time
                        from success left join student on suc_student_id=student_id 
                        left join  class on student_class_Id=class_id
                        left join sub on class_sub_id = sub_id
                        left join teaching on suc_teaching_id=teaching_id where student_state=1");
            return new XHCTMS.DBHELP.Common().GetDataTable(sb.ToString());
        }

        /// <summary>
        /// 增加学生成绩
        /// </summary>
        /// <param name="studentId">学生编号</param>
        /// <param name="teachingId">课程编号</param>
        /// <returns>返回类型int 成功/1 失败/0 异常/-1</returns>
        public int Add(int studentId, int teachingId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("p_InsertSucces");
            SqlParameter[] paras ={
                                     new SqlParameter("@studentId",SqlDbType.Int),
                                     new SqlParameter("@teachingId",SqlDbType.Int)
                                 };
            paras[0].Value = studentId;
            paras[1].Value = teachingId;

            return new XHCTMS.DBHELP.Common().ExcuteProcQuery(sb.ToString(),paras);
        }


        public int TeachingEnding(int teaID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select tstu.student_id from teaching tte left join class  tcl on  tte.teaching_class_Id = tcl.class_id
           left join student tstu
             on tcl.class_id = tstu.student_class_Id where tte.teaching_id =@teaID  ");
            sb.Append(@"declare @check decimal(5,2)      
       select  @check = SUM(checking_score) from checking where checking_student_id ='20' and checking_teaching_id = '100003'");
            sb.Append(@"   declare @perfor decimal(5,2)
       select @perfor = SUM(Per_score) from performance where Per_student_id =4 and Per_teaching_id = 100000   ");
            return -1;
        }

        /// <summary>
        /// 获得某结课未打分课程的所有学生信息
        /// </summary>
        /// <param name="teaID">课程号</param>
        /// <returns>返回类型DataTable</returns>
        public DataTable GetTeachingEndStuInfo(int teaID)
        {
            StringBuilder sb = new StringBuilder("");
            SqlParameter[] paras = { new SqlParameter(@"teaID", teaID) };
            sb.Append(@"select tcl.class_name,tte.teaching_lesson,
        tstu.student_name,tte.teaching_time ,tstu.student_id , 
 ( select SUM(checking_score)  from checking where  checking_teaching_id = @teaID and checking_student_id = tte.teaching_id)
 as checkScore,
( select SUM(Per_score)  from performance where  Per_teaching_id = @teaID and Per_student_id = tte.teaching_id)as perScore
 from  student tstu left join class tcl on tstu.student_class_Id = tcl.class_id
   left join teaching tte on tte.teaching_class_Id = tcl.class_id 
 where tte.teaching_state = 0 and tte.teaching_id =@teaID ");
           return new DBHELP.Common().GetDataTable(sb.ToString(), paras);

        }


        /// <summary>
        /// 获取某结课未打分课程的所有班级信息
        /// </summary>
        /// <param name="teaID">课程号</param>
        /// <returns>返回类型DataTable</returns>
        public DataTable GetTeachingEndClassInfo(int teaID)
        {

            StringBuilder sb = new StringBuilder("");
            SqlParameter[] paras = { new SqlParameter(@"teaID", teaID) };
            sb.Append(@" select tu.[user_name],tcl.class_name,tte.teaching_lesson,tsub.sub_name , 
        tteu.[user_name]  as tea_name from 
        class tcl left join [user] tu on tcl.class_teacher_id = tu.[user_id]
        left join sub tsub on tcl.class_sub_id = tsub.sub_id 
        left join teaching tte on tte.teaching_class_Id = tcl.class_id 
        left join [user] tteu on  tteu.[user_id] = tte.teaching_teacher_Id
       where tte.teaching_state = 0 and tte.teaching_id =@teaID");
            return new DBHELP.Common().GetDataTable(sb.ToString(), paras);

    
        }



        /// <summary>
        /// 讲打过分的课程里所有学生插入信息
        /// </summary>
        /// <param name="lstSql"></param>
        /// <returns></returns>
        public int AddClassTeaStu(List<string> lstSql ,int teaID)
        {
            StringBuilder sb = new StringBuilder();
            foreach (string str in lstSql)
            {
                sb.Append(str);
            }
            SqlParameter[] paras = { new SqlParameter("@teaID",teaID)};
            sb.Append(" update teaching  set teaching_state = -1 ,teaching_strstate ='已打分' where teaching_id = @teaID;");
            sb.Append(@" declare @average  decimal(5,2)
                select @average = avg(suc_total) from success where  suc_teaching_id = 100020
                insert into fraction values(@teaID,@average,CONVERT(varchar(32),getdate(),23));");
            return new DBHELP.Common().ExcuteQuery(sb.ToString(),paras);
        }


        /// <summary>
        /// 查找50项符合要求的学生信息（未毕业）
        /// </summary>
        /// <returns></returns>
        public DataTable Search(string[] parasValue, int page)
        { //classname classtea teaingtea subname   tealess  stuname
            DataTable dt = new DataTable();

            string[] parasString = { " tcl.class_name =", " tclu.[user_name] =", " tteu.[user_name] ="," tsub.sub_name =",
                                   " tte.teaching_lesson like "," tstu.student_name like "};
            string[] parasName = { "@className", "@classTeaName", "@TeachingTeaName", "@subName", "@classLesson", "@studentName" };
            if (parasValue.Length > parasString.Length)
            {
                dt.TableName = "ArraryError";
                return dt;
            }



            List<SqlParameter> lstParas = new List<SqlParameter>();
            StringBuilder sb = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            sb.Append(@"select top 50 (tsub.sub_name+' '+ convert(varchar(2),tsub.sub_years)+'年制'+' '+tcl.class_name) 
        as class_allname,tstu.student_name,tte.teaching_lesson,
        tclu.[user_name] as class_tea , tteu.[user_name] as teaching_tea ,tsuc.suc_Total,tsuc.suc_time
        from success tsuc left join teaching  tte on tsuc.suc_teaching_id = tte.teaching_id
        left join student tstu on tsuc.suc_student_id = tstu.student_id
        left join class tcl on tte.teaching_class_Id=tcl.class_id
        left join sub tsub on tsub.sub_id = tcl.class_sub_id
        left join [user] tclu on tcl.class_teacher_id = tclu.[user_id]
        left join [user] tteu on tte.teaching_teacher_Id = tteu.[user_id]
        where tstu.student_state= 1 ");
            sb2.Append(@" and tsuc.suc_id not in(
        select top (50*@page) suc_id from success tsuc left join teaching  tte 
        on tsuc.suc_teaching_id = tte.teaching_id
        left join student tstu on tsuc.suc_student_id = tstu.student_id
        left join class tcl on tte.teaching_class_Id=tcl.class_id
        left join sub tsub on tsub.sub_id = tcl.class_sub_id
        left join [user] tclu on tcl.class_teacher_id = tclu.[user_id]
        left join [user] tteu on tte.teaching_teacher_Id = tteu.[user_id] 
        where  tstu.student_state= 1 ");
            for (int count = 0; count < parasValue.Length; count++)
            {

                if (!string.IsNullOrEmpty(parasValue[count]))
                {
                    if (count == 4 || count == 5)
                    {
                        StringBuilder arrSb = new StringBuilder();
                        for (int i = 0; i < parasValue[count].Length; i++)
                        {
                            arrSb.Append("%" + parasValue[count].Substring(i, 1));
                        }
                        arrSb.Append("%");
                        parasValue[count] = arrSb.ToString();
                    }
                    sb.Append(" and " + parasString[count] + parasName[count]);
                    sb2.Append(" and " + parasString[count] + parasName[count]);
                    lstParas.Add(new SqlParameter(parasName[count], parasValue[count]));
                }
            }


            lstParas.Add(new SqlParameter("@page", page - 1));
            sb.Append(sb2.ToString());
                sb.Append(@" order by tsub.sub_name,tsub.sub_years,tcl.class_name,tte.teaching_lesson,tstu.student_name
            ) order by class_allname,tte.teaching_lesson,tstu.student_name");

            return dt = new DBHELP.Common().GetDataTable(sb.ToString(), lstParas);
        }



        /// <summary>
        /// 获取满足条件的所有学生成绩信息的页数（每页50条）
        /// </summary>
        /// <param name="parasValue"></param>
        /// <returns></returns>
        public int MaxPage(string[] parasValue)
        {
            string[] parasString = { " tcl.class_name=", " tclu.[user_name] =", " tteu.[user_name] ="," tsub.sub_name=",
                                   " tte.teaching_lesson like "," tstu.student_name like "};
            string[] parasName = { "@className", "@classTeaName", "@TeachingTeaName", "@subName", "@classLesson", "@studentName" };
            if (parasValue.Length > parasString.Length)
            {
                return -11;
            }
            List<SqlParameter> lstParas = new List<SqlParameter>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select COUNT(tsuc.suc_id)
        from success tsuc left join teaching  tte on tsuc.suc_teaching_id = tte.teaching_id
        left join student tstu on tsuc.suc_student_id = tstu.student_id
        left join class tcl on tte.teaching_class_Id=tcl.class_id
        left join sub tsub on tsub.sub_id = tcl.class_sub_id
        left join [user] tclu on tcl.class_teacher_id = tclu.[user_id]
        left join [user] tteu on tte.teaching_teacher_Id = tteu.[user_id]
        where tstu.student_state= 1 ");
            for (int count = 0; count < parasValue.Length; count++)
            {
                
                if (!string.IsNullOrEmpty(parasValue[count]))
                {

                    if (count == 4 || count == 5)
                    {
                        StringBuilder arrSb = new StringBuilder();
                        for (int i = 0; i < parasValue[count].Length; i++)
                        {
                            arrSb.Append("%" + parasValue[count].Substring(i, 1));
                        }
                        arrSb.Append("%");
                        parasValue[count] = arrSb.ToString();
                    }

                    sb.Append(" and " + parasString[count] + parasName[count]);                 
                    lstParas.Add(new SqlParameter(parasName[count], parasValue[count]));
                }
            }
           
            int number = int.Parse(new DBHELP.Common().GetDataScalar(sb.ToString(),lstParas));
            return (number - 1) / 50 + 1;

        }


    }
}
